

*------------------------------------------------------------------------------*
* Import the data
*------------------------------------------------------------------------------*
*---------------------- 1. bitcoin
* Import the excel
insheet using "data/source/1. bitcoin.csv",clear names

* Generate date
rename date date_o
gen date = date(date_o,"YMD")
order date
format date %td


* Scale supply in circulation
gen supply = splycur / 1000000


* Set date
rename priceusd price
label var price "bitcoin price ($)"
tsset date
	
gen type = 	"bitcoin"  
save "data/working/1. bitcoin.dta",replace



*---------------------- 2. ethereum
* Import the excel
insheet using "data/source/2. ethereum.csv",clear names

* Generate date
rename date date_o
gen date = date(date_o,"YMD")
order date
format date %td


* Scale supply in circulation
gen supply = splycur / 1000000


* Set date
rename priceusd price
label var price "ethereum price ($)"
tsset date

gen type = 	"ethereum"  
save "data/working/2. ethereum.dta",replace


*---------------------- 3. itecoin
* Import the excel
insheet using "data/source/3. litecoin.csv",clear names

* Generate date
rename date date_o
gen date = date(date_o,"YMD")
order date
format date %td

* Scale supply in circulation
gen supply = splycur / 1000000


* Set date
rename priceusd price
label var price "litecoin price ($)"
tsset date

gen type = 	"litecoin"
save "data/working/3. litecoin.dta",replace
   
	   
	   
*---------------------- 4. bitcoin_cash
* Import the excel
insheet using "data/source/4. bitcoin_cash.csv",clear names


* Generate date
rename date date_o
gen date = date(date_o,"YMD")
order date
format date %td


* Scale supply in circulation
gen supply = splycur / 1000000


* Set date
rename priceusd price
label var price "bitcoin cash price ($)"
tsset date

gen type = 	"bitcoin_cash"  	   
save "data/working/4. bitcoin_cash.dta", replace   
	   
	 
	   
*---------------------- 5. zcash
* Import the excel
insheet using "data/source/5. zcash.csv",clear names


* Generate date
rename date date_o
gen date = date(date_o,"YMD")
order date
format date %td


* Scale supply in circulation
gen supply = splycur / 1000000


* Set date
rename priceusd price
label var price "zcash price ($)"
tsset date
	   
gen type = 	"zcash"  	   
save "data/working/5. zcash.dta", replace
	
	
	
*---------------------- 6. Dash
* Import the excel
insheet using "data/source/6. Dash.csv",clear names


* Generate date
rename date date_o
gen date = date(date_o,"YMD")
order date
format date %td

* Scale supply in circulation
gen supply = splycur / 1000000


* Set date
rename priceusd price
label var price "Dash price ($)"
tsset date   

gen type = 	"dash"  	   
save "data/working/6. Dash.dta", replace   
	   
	 
	   
*---------------------- 7. monero
* Import the excel
insheet using "data/source/7. menero.csv",clear names


* Generate date
rename date date_o
gen date = date(date_o,"YMD")
order date
format date %td


* Scale supply in circulation
gen supply = splycur / 1000000


* Set date
rename priceusd price
label var price "monero price ($)"
tsset date
   

gen type = 	"monero"  
save "data/working/7. monero.dta"	, replace  


*---------------------- 8. xrp
* Import the excel
insheet using "data/source/8. xrp.csv",clear names


* Generate date
rename date date_o
gen date = date(date_o,"YMD")
order date
format date %td


* Scale supply in circulation
gen supply = splycur / 1000000


* Set date
rename priceusd price
label var price "xrp price ($)"
tsset date	   

gen type = 	"ripple"  
save "data/working/8. xrp.dta"	, replace  
	   

	   
	   
*---------------------- Other
* Import the excel
insheet using "data/source/9-1. cardano.csv",clear names
gen currency_id = "cardano" 
save "data/working/9-1. cardano.dta"	, replace

insheet using "data/source/9-2. binancecoin.csv",clear names
gen currency_id = "binancecoin" 
save "data/working/9-2. binancecoin.dta"	, replace 

insheet using "data/source/9-3. stellar.csv",clear names
gen currency_id = "stellar" 
save "data/working/9-3. stellar.dta"	, replace 

insheet using "data/source/9-4. bitcoinsv.csv",clear names
gen currency_id = "bitcoinsv" 
save "data/working/9-4. bitcoinsv.dta"	, replace 

insheet using "data/source/9-5. usdcoin.csv",clear names
gen currency_id = "usdcoin" 
save "data/working/9-5. usdcoin.dta"	, replace 

insheet using "data/source/9-6. monero.csv",clear names
gen currency_id = "monero" 
save "data/working/9-6. monero.dta"	, replace 

insheet using "data/source/9-7. eos.csv",clear names
gen currency_id = "eos" 
save "data/working/9-7. eos.dta"	, replace 

insheet using "data/source/9-8. tron.csv",clear names
gen currency_id = "tron" 
save "data/working/9-8. tron.dta"	, replace 

insheet using "data/source/9-9. nem.csv",clear names
gen currency_id = "nem" 
save "data/working/9-9. nem.dta"	, replace 

insheet using "data/source/9-10. tezos.csv",clear names
gen currency_id = "tezos" 
save "data/working/9-10. tezos.dta"	, replace 

insheet using "data/source/9-11. cryptocom.csv",clear names
gen currency_id = "cryptocom" 
save "data/working/9-11. cryptocom.dta"	, replace 

insheet using "data/source/9-13. dai.csv",clear names
gen currency_id = "dai" 
save "data/working/9-13. dai.dta"	, replace 


* Append all Others
use "data/working/9-1. cardano.dta", clear
append using "data/working/9-2. binancecoin.dta"
append using "data/working/9-3. stellar.dta"
append using "data/working/9-4. bitcoinsv.dta"
append using "data/working/9-5. usdcoin.dta"
append using "data/working/9-6. monero.dta"
append using "data/working/9-10. tezos.dta"
append using "data/working/9-13. dai.dta"
save "data/working/9. alt_tem.dta",replace



* Generate date
use "data/working/9. alt_tem.dta", clear
rename date date_o
gen date = date(date_o,"YMD")
order date
format date %td


* Scale supply in circulation
gen supply = splycur / 1000000


* Calculate the mean of the top 10 other currencies 
keep supply priceusd currency_id date
rename priceusd priceo
rename supply supplyo


* Simple average
sort date currency_id
bys date: egen price = mean(priceo)
bys date: egen supply = mean(supplyo)

* Weighted average price
gen priceo_supplyo = priceo*supplyo
bys date: egen sum_priceo_supplyo_tem = sum(priceo_supplyo)
bys date: egen sum_supplyo = sum(supplyo)

gen price_w = sum_priceo_supplyo_tem / sum_supplyo
replace price_w = . if price_w == 0
gen supply_w = sum_priceo_supplyo_tem /price_w


* Format the sample
duplicates drop date, force
drop currency_id priceo supplyo
gen currency_id = 	"ALT"

label var price "ALT price ($)"
tsset date


rename currency_id type
save "data/working/9. alt.dta"	, replace 

	   
	   
*---------------------- Append all together
use "data/working/2. ethereum.dta",clear
append using "data/working/1. bitcoin.dta"
append using "data/working/3. litecoin.dta"
append using "data/working/4. bitcoin_cash.dta"
append using "data/working/5. zcash.dta"
append using "data/working/6. Dash.dta"
append using "data/working/7. monero.dta"	
append using "data/working/8. xrp.dta"
append using "data/working/9. alt.dta"

rename type currency_id

save  "data/working/cryptocurrency.dta",replace 


